package com.ruoyi.system.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.SecureUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.TBaseCost;
import com.ruoyi.system.domain.TBaseProject;
import com.ruoyi.system.domain.TSpCountIncome;
import com.ruoyi.system.mapper.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.domain.TSpCountTax;
import com.ruoyi.system.service.ITSpCountTaxService;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;

/**
 * 项目税款利润统计Service业务层处理
 * 
 * @author ruoyi
 * @date 2024-03-21
 */
@Service
public class TSpCountTaxServiceImpl extends ServiceImpl<TSpCountTaxMapper, TSpCountTax> implements ITSpCountTaxService
{
    @Autowired
    private TSpCountTaxMapper tSpCountTaxMapper;

    @Autowired
    private TSpProjectDetailMapper projectDetailMapper;

    @Autowired
    private TBaseProjectMapper projectMapper;

    @Autowired
    private TSpCountIncomeMapper countIncomeMapper;

    @Autowired
    private TSpCountIncomeHistoryMapper countIncomeHistoryMapper;

    @Autowired
    private TBaseCostMapper costMapper;


    /**
     * 查询项目税款利润统计
     * 
     * @param projectId 项目税款利润统计主键
     * @return 项目税款利润统计
     */
    @Override
    public TSpCountTax selectTSpCountTaxByProjectId(TSpCountTax tSpCountTax)
    {
        return tSpCountTaxMapper.selectTSpCountTaxByProjectId(tSpCountTax);
    }

    /**
     * 利润合计
     *
     */
    @Override
    public BigDecimal selectProfitSum(TSpCountTax tSpCountTax) {
        return tSpCountTaxMapper.selectProfitSum(tSpCountTax);
    }

    /**
     * 项目金额
     *
     * @param tSpCountTax
     */
    @Override
    public BigDecimal selectProjectMoney(TSpCountTax tSpCountTax) {
        return tSpCountTaxMapper.selectProjectMoney(tSpCountTax);
    }

    /**
     * 利润合计  历史记录
     */
    @Override
    public BigDecimal selectProfitSumHistory(TSpCountTax tSpCountTax) {
        List<TSpCountTax> list = null;
        list = tSpCountTaxMapper.selectTSpCountTaxListHistory(tSpCountTax);
        if(list == null || list.size() == 0){
            return tSpCountTaxMapper.selectProfitSum(tSpCountTax);
        }else {
            return tSpCountTaxMapper.selectProfitSumHistory(tSpCountTax);
        }
    }

    /**
     * 查询项目税款利润统计列表
     * 
     * @param tSpCountTax 项目税款利润统计
     * @return 项目税款利润统计
     */
    @Override
    public List<TSpCountTax> selectTSpCountTaxList(TSpCountTax tSpCountTax)
    {
        List<TSpCountTax> list = tSpCountTaxMapper.selectTSpCountTaxList(tSpCountTax);
        list.forEach(map -> {
            Long projectId = map.getProjectId();
            String countDate = map.getCountDate();
            List<TSpCountIncome> countIncomeIncomeList = countIncomeMapper.selectTSpCountIncomeList(TSpCountIncome.builder().projectId(projectId).countDate(countDate).costType("1").build());
            List<TSpCountIncome> countIncomeDisburseList = countIncomeMapper.selectTSpCountIncomeList(TSpCountIncome.builder().projectId(projectId).countDate(countDate).costType("2").build());
            map.setCountIncomeIncomeList(countIncomeIncomeList);
            map.setCountIncomeDisburseList(countIncomeDisburseList);
        });
        return list;
    }

    /**
     * 查询项目税款利润统计列表  历史记录
     *
     * @param tSpCountTax 项目税款利润统计
     * @return 项目税款利润统计集合
     */
    @Override
    public List<TSpCountTax> selectTSpCountTaxListHistory(TSpCountTax tSpCountTax) {
        List<TSpCountTax> list = null;
        list = tSpCountTaxMapper.selectTSpCountTaxListHistory(tSpCountTax);
        if(list == null || list.size() == 0){
            list = tSpCountTaxMapper.selectTSpCountTaxList(tSpCountTax);
            list.forEach(map -> {
                Long projectId = map.getProjectId();
                String countDate = map.getCountDate();
                List<TSpCountIncome> countIncomeIncomeList = countIncomeMapper.selectTSpCountIncomeList(TSpCountIncome.builder().projectId(projectId).countDate(countDate).costType("1").build());
                List<TSpCountIncome> countIncomeDisburseList = countIncomeMapper.selectTSpCountIncomeList(TSpCountIncome.builder().projectId(projectId).countDate(countDate).costType("2").build());
                map.setCountIncomeIncomeList(countIncomeIncomeList);
                map.setCountIncomeDisburseList(countIncomeDisburseList);
            });
        }else{
            list.forEach(map -> {
                Long projectId = map.getProjectId();
                String countDate = map.getCountDate();
                List<TSpCountIncome> countIncomeIncomeList = countIncomeHistoryMapper.selectTSpCountIncomeList(TSpCountIncome.builder().projectId(projectId).countDate(countDate).costType("1").build());
                List<TSpCountIncome> countIncomeDisburseList = countIncomeHistoryMapper.selectTSpCountIncomeList(TSpCountIncome.builder().projectId(projectId).countDate(countDate).costType("2").build());
                map.setCountIncomeIncomeList(countIncomeIncomeList);
                map.setCountIncomeDisburseList(countIncomeDisburseList);
            });
        }
        list.forEach(map -> {
            //收入合计  收入合计=应收金额+除物业费以外的所有收入
            BigDecimal incomeMoney = map.getCountIncomeIncomeList().stream().filter(o -> o.getCostId() != 9).map(p -> p.getTotalMoney()).reduce(BigDecimal.ZERO,BigDecimal :: add);
            incomeMoney = incomeMoney.add(map.getBillMoney());
            map.setIncomeMoney(incomeMoney);
            //税额
            BigDecimal taxMoney = map.getTaxRate().multiply(map.getBillMoney()).divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
            map.setTaxMoney(taxMoney);
        });
        return list;
    }

    /**
     * 新增项目税款利润统计
     * 
     * @param tSpCountTax 项目税款利润统计
     * @return 结果
     */
    @Override
    public int insertTSpCountTax(TSpCountTax tSpCountTax)
    {
        return tSpCountTaxMapper.insertTSpCountTax(tSpCountTax);
    }

    /**
     * 修改项目税款利润统计
     * 
     * @param tSpCountTax 项目税款利润统计
     * @return 结果
     */
    @Override
    public int updateTSpCountTax(TSpCountTax tSpCountTax)
    {
        //税率
        BigDecimal taxRate = tSpCountTax.getTaxRate();
        //开票金额
        BigDecimal billMoney = tSpCountTax.getBillMoney();
        if(ObjectUtil.isNull(taxRate) || ObjectUtil.isNull(billMoney)){
            throw new RuntimeException("税率和开票金额不能为空");
        }
        TSpCountTax one = tSpCountTaxMapper.selectOne(new LambdaQueryWrapper<TSpCountTax>()
                .eq(TSpCountTax::getProjectId, tSpCountTax.getProjectId())
                .eq(TSpCountTax::getCountDate, tSpCountTax.getCountDate()));
        //收入合计
        BigDecimal incomeMoney = one.getIncomeMoney();
        //支出合计
        BigDecimal payMoney = one.getPayMoney();

        //税额
        BigDecimal taxMoney = BigDecimal.ZERO;

        incomeMoney = incomeMoney.add(billMoney);
        taxMoney = taxRate.multiply(billMoney).divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
        payMoney = payMoney.add(taxMoney);


        //利润合计
        BigDecimal profitMoney = incomeMoney.subtract(payMoney);
        //利润÷收入×100=利润率
        BigDecimal profitRate = (BigDecimal.ZERO.compareTo(incomeMoney) == 0 ? BigDecimal.ZERO : profitMoney.divide(incomeMoney, 2, RoundingMode.HALF_UP)).multiply(new BigDecimal(100));
        tSpCountTax.setIncomeMoney(incomeMoney);
        tSpCountTax.setTaxMoney(taxMoney);
        tSpCountTax.setPayMoney(payMoney);
        tSpCountTax.setProfitMoney(profitMoney);
        tSpCountTax.setProfitRate(profitRate);
        tSpCountTax.setUpdateDate(DateUtils.getNowDate());
        tSpCountTax.setUpdatePerson(SecurityUtils.getUserId().toString());
        return tSpCountTaxMapper.updateTSpCountTax(tSpCountTax);
    }

    /**
     * 修改项目税款利润统计   历史记录
     *
     * @param tSpCountTax 项目税款利润统计
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int updateTSpCountTaxHistory(List<TSpCountTax> list) {
        Long projectId = list.get(0).getProjectId();
        //数据全部删除  重新添加
        tSpCountTaxMapper.deleteTSpCountTaxByProjectIdHistory(projectId);
        countIncomeHistoryMapper.deleteTSpCountIncomeByProjectId(projectId);
        for (TSpCountTax tSpCountTax : list) {
            List<TSpCountIncome> countIncomeIncomeList = tSpCountTax.getCountIncomeIncomeList();
            List<TSpCountIncome> countIncomeDisburseList = tSpCountTax.getCountIncomeDisburseList();

            //收入合计  收入合计=应收金额+除物业费以外的所有收入
            BigDecimal incomeMoney = countIncomeIncomeList.stream().filter(o -> o.getCostId() != 9).map(p -> p.getTotalMoney()).reduce(BigDecimal.ZERO,BigDecimal :: add);
            incomeMoney = incomeMoney.add(tSpCountTax.getBillMoney());

            //税额
            BigDecimal taxMoney = tSpCountTax.getTaxRate().multiply(tSpCountTax.getBillMoney()).divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
            //支出合计
            BigDecimal payMoney = countIncomeDisburseList.stream().map(p -> p.getTotalMoney()).reduce(BigDecimal.ZERO,BigDecimal :: add);
            payMoney = payMoney.add(taxMoney);
            //利润合计
            BigDecimal profitMoney = incomeMoney.subtract(payMoney);
            //利润÷收入X100=利润率
            BigDecimal profitRate = (BigDecimal.ZERO.compareTo(incomeMoney) == 0 ? BigDecimal.ZERO : profitMoney.divide(incomeMoney, 3, BigDecimal.ROUND_HALF_UP)).multiply(new BigDecimal(100));

            tSpCountTax.setIncomeMoney(incomeMoney);
            tSpCountTax.setTaxMoney(taxMoney);
            tSpCountTax.setPayMoney(payMoney);
            tSpCountTax.setProfitMoney(profitMoney);
            tSpCountTax.setProfitRate(profitRate);
            tSpCountTaxMapper.insertTSpCountTaxHistory(tSpCountTax);

            if(countIncomeIncomeList != null && countIncomeIncomeList.size() > 0){
                countIncomeIncomeList.forEach(map -> {
                    map.setCreateDate(DateUtils.getNowDate());
                    map.setCreatePerson("SYSTEM");
                    map.setProjectId(projectId);
                    map.setCountDate(tSpCountTax.getCountDate());
                });
                countIncomeHistoryMapper.addBatchTSpCountIncome(countIncomeIncomeList);
            }
            if(countIncomeDisburseList != null && countIncomeDisburseList.size() > 0){
                countIncomeDisburseList.forEach(map -> {
                    map.setCreateDate(DateUtils.getNowDate());
                    map.setCreatePerson("SYSTEM");
                    map.setProjectId(projectId);
                    map.setCountDate(tSpCountTax.getCountDate());
                });
                countIncomeHistoryMapper.addBatchTSpCountIncome(countIncomeDisburseList);
            }
        }
        return 1;
    }

    /**
     * 批量删除项目税款利润统计
     * 
     * @param projectIds 需要删除的项目税款利润统计主键
     * @return 结果
     */
    @Override
    public int deleteTSpCountTaxByProjectIds(Long[] projectIds)
    {
        return tSpCountTaxMapper.deleteTSpCountTaxByProjectIds(projectIds);
    }

    /**
     * 删除项目税款利润统计信息
     * 
     * @param projectId 项目税款利润统计主键
     * @return 结果
     */
    @Override
    public int deleteTSpCountTaxByProjectId(Long projectId)
    {
        return tSpCountTaxMapper.deleteTSpCountTaxByProjectId(projectId);
    }

    /**
     * 月流水数据统计
     */
    @Override
//    @Transactional(rollbackFor = Exception.class)
    public void monthStatistics(String lastYearMonth, Long projectId) {
        if(StrUtil.isBlank(lastYearMonth)){
            //上月Str  202402
            lastYearMonth = DateUtil.format(DateUtil.lastMonth(), "yyyyMM");
        }
        List<Map<String, Object>> list = projectDetailMapper.selectProjectDetailByLastMonth(projectId.toString(), lastYearMonth);
        //收入合计
        BigDecimal incomeMoney = list.stream().filter(o -> "1".equals(o.get("cost_type"))).map(p -> (BigDecimal)p.get("sumCostMoney")).reduce(BigDecimal.ZERO,BigDecimal :: add);
        //支出合计
        BigDecimal payMoney = list.stream().filter(o -> "2".equals(o.get("cost_type"))).map(p -> (BigDecimal)p.get("sumCostMoney")).reduce(BigDecimal.ZERO,BigDecimal :: add);
        //利润合计
        BigDecimal profitMoney = incomeMoney.subtract(payMoney);
        //利润÷支出×100=利润率
        BigDecimal profitRate = profitMoney.divide(payMoney, 2, RoundingMode.HALF_UP).multiply(new BigDecimal(100));
        TSpCountTax tax = new TSpCountTax();
        tax.setProjectId(projectId);
        tax.setCountDate(lastYearMonth);
        tax.setIncomeMoney(incomeMoney);
        tax.setPayMoney(payMoney);
        tax.setProfitMoney(profitMoney);
        tax.setProfitRate(profitRate);
        tax.setCreateDate(DateUtils.getNowDate());
        tax.setCreatePerson("SYSTEM");
        tSpCountTaxMapper.insertTSpCountTax(tax);
        List<TSpCountIncome> incomeList = new ArrayList<>();
        TSpCountIncome income ;
        for (int j = 0; j < list.size(); j++) {
            Map<String, Object> map1 = list.get(j);
            income = new TSpCountIncome();
            income.setProjectId(projectId);
            income.setCountDate(lastYearMonth);
            income.setCostId((Long) map1.get("cost_id"));
            income.setTotalMoney((BigDecimal) map1.get("sumCostMoney"));
            income.setCreateDate(DateUtils.getNowDate());
            income.setCreatePerson("SYSTEM");
            incomeList.add(income);
        }
        //批量添加
        countIncomeMapper.addBatchTSpCountIncome(incomeList);
    }

    /**
     * 导出
     */
    @Override
    public void exportExcel(TSpCountTax tSpCountTax,HttpServletResponse response) {
        List<TSpCountTax> tSpCountTaxes = this.selectTSpCountTaxList(tSpCountTax);
        TBaseProject tBaseProject = projectMapper.selectTBaseProjectByProjectId(tSpCountTax.getProjectId());
        //处理只传时间没有项目的  问题
        if(ObjectUtil.isEmpty(tBaseProject)){
            tBaseProject = new TBaseProject();
            tBaseProject.setProjectName(tSpCountTax.getBeginTime()+"-"+tSpCountTax.getEndTime() + " 统计");
            tBaseProject.setBeginDate(DateUtil.parse(tSpCountTax.getBeginTime(),"yyyyMM").toJdkDate());
            int day = DateUtil.parse(tSpCountTax.getEndTime(), "yyyyMM").getLastDayOfMonth();
            tBaseProject.setEndDate(DateUtil.parse(tSpCountTax.getEndTime()+day,"yyyyMMdd").toJdkDate());
            BigDecimal projectMoney = tSpCountTaxMapper.selectProjectMoney(tSpCountTax);
            tBaseProject.setProjectMoney(projectMoney);
        }
        TBaseCost cost = new TBaseCost();
        cost.setCostType("1");
        cost.setProjectId(tSpCountTax.getProjectId());
        cost.setBeginTime(tSpCountTax.getBeginTime());
        cost.setEndTime(tSpCountTax.getEndTime());
        List<TBaseCost> costInList = costMapper.selectTBaseCostList(cost);
        cost = new TBaseCost();
        cost.setCostType("2");
        cost.setProjectId(tSpCountTax.getProjectId());
        cost.setBeginTime(tSpCountTax.getBeginTime());
        cost.setEndTime(tSpCountTax.getEndTime());
        List<TBaseCost> costOutList = costMapper.selectTBaseCostList(cost);
        BigDecimal profitSum = tSpCountTaxMapper.selectProfitSum(tSpCountTax);
        this.excel(tBaseProject,tSpCountTaxes,costInList,costOutList,profitSum,response);
    }

    /**
     * 导出  历史记录
     */
    @Override
    public void exportExcelHistory(TSpCountTax tSpCountTax,HttpServletResponse response) {
        List<TSpCountTax> tSpCountTaxes = this.selectTSpCountTaxListHistory(tSpCountTax);
        TBaseProject tBaseProject = projectMapper.selectTBaseProjectByProjectId(tSpCountTax.getProjectId());
        List<TSpCountTax> list = tSpCountTaxMapper.selectTSpCountTaxListHistory(tSpCountTax);
        TBaseCost cost = new TBaseCost();
        if(list == null || list.size() == 0){
            cost.setProjectId(tSpCountTax.getProjectId());
        }else {
            cost.setHisprojectId(tSpCountTax.getProjectId());
        }
        cost.setCostType("1");
        List<TBaseCost> costInList = costMapper.selectTBaseCostList(cost);
        cost.setCostType("2");
        List<TBaseCost> costOutList = costMapper.selectTBaseCostList(cost);
        BigDecimal profitSum = this.selectProfitSumHistory(tSpCountTax);
        this.excel(tBaseProject,tSpCountTaxes,costInList,costOutList,profitSum,response);

    }

    /**
     * 生成Excel
     * @param project
     * @param taxList
     * @param costInList
     * @param costOutList
     * @param profitSum 利润合计
     */
    private void excel(TBaseProject project, List<TSpCountTax> taxList, List<TBaseCost> costInList,List<TBaseCost> costOutList,BigDecimal profitSum,HttpServletResponse response){
        //总列数
        int cell = costInList.size() + costOutList.size() + 9;
        //收入统计列数
        int inCount = Integer.parseInt(String.valueOf(costInList.size())) + 2;
        //支出统计列数
//        int outCount = Integer.parseInt(String.valueOf(costOutList.size())) + 2;

        /** 第一步，创建一个Workbook，对应一个Excel文件  */
        SXSSFWorkbook wb = new SXSSFWorkbook();
        DataFormat dataFormat = wb.createDataFormat();

        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
        Sheet sheet = wb.createSheet("统计");
        /** 第三步，设置样式以及字体样式*/
        //标题样式
        CellStyle titleStyle = createTitleCellStyle(wb);

        //二三行样式
        CellStyle titleStyleLeft = createTitleCellStyleLeft(wb);
        //创建表头样式
        CellStyle headerStyle = createHeadCellStyle(wb);
        //创建表头样式  居中
        CellStyle headerStyleCenter = createHeadCellStyleCenter(wb);
        //创建表头样式  居中 利润
        CellStyle headerStyleCenterLirun = createHeadCellStyleCenterLirun(wb);
        //内容样式
        CellStyle contentStyle = createContentCellStyle(wb);
        //收入合计支出合计样式
        CellStyle contentCellStyleHeji = createContentCellStyleHeji(wb);
        //利润样式
        CellStyle contentCellStyleLirun = createContentCellStyleLirun(wb);

        //设置全局 单元格  默认 宽度
        sheet.setDefaultColumnWidth(12);

        /** 第四步，创建标题 ,合并标题单元格 */
        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        Row row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 600);// 设置行高

        String title = project.getProjectName();
        Cell c00 = row0.createCell(0);
        c00.setCellValue(title);
        c00.setCellStyle(titleStyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cell - 1));//标题合并单元格操作，17为总列数

        // 第二行
        Row row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 600);
        Cell tempCell = row1.createCell(0);
        tempCell.setCellStyle(titleStyleLeft);
        tempCell.setCellValue("项目周期： "+DateUtils.parseDateToStr("yyyy.MM.dd",project.getBeginDate()) +"-"+DateUtils.parseDateToStr("yyyy.MM.dd",project.getEndDate()));
        // 合并
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, cell - 1));

        // 第三行
        Row row2 = sheet.createRow(rowNum++);
        row2.setHeight((short) 600);
        Cell tempCell2 = row2.createCell(0);
        tempCell2.setCellStyle(titleStyleLeft);
        tempCell2.setCellValue("项目金额：     "+project.getProjectMoney());
        // 合并
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, cell - 3));


        Cell tempCell22 = row2.createCell(cell - 2);
        tempCell22.setCellStyle(titleStyleLeft);
        tempCell22.setCellValue("利润合计： "+profitSum);
        // 合并
        sheet.addMergedRegion(new CellRangeAddress(2, 2, cell - 2, cell - 1));

        //第四行
        Row row3 = sheet.createRow(rowNum++);
        row3.setHeight((short) 400);
        for (int i = 0; i < cell; i++) {
            Cell tempCell3 = row3.createCell(i);
            tempCell3.setCellStyle(headerStyleCenter);
            if (i == 0) {
                tempCell3.setCellValue("项目");
            }else if (i == 1) {
                tempCell3.setCellValue("日期");
            } else if (i == 2) {
                tempCell3.setCellValue("税率");
            } else if(i == 3) {
                tempCell3.setCellValue("收入");
            } else if(i == inCount + 3) {
                tempCell3.setCellValue("支出");
            } else if(i == cell - 2) {
                tempCell3.setCellStyle(headerStyleCenterLirun);
                tempCell3.setCellValue("利润");
            } else if(i == cell - 1) {
                tempCell3.setCellStyle(headerStyleCenterLirun);
                tempCell3.setCellValue("利润率");
            }
        }
        //第伍行
        Row row4 = sheet.createRow(rowNum++);
        row4.setHeight((short) 400);
        for (int i = 0; i < cell; i++) {
            Cell tempCell4 = row4.createCell(i);
            tempCell4.setCellStyle(headerStyleCenter);
            if(i == 3) {
                tempCell4.setCellValue("应收金额");
            } else if(i >= 4 && i < inCount + 2) {
                for (int j = 0; j < costInList.size(); j++) {
                    if(i == 4 + j){
                        tempCell4.setCellValue(costInList.get(j).getCostName());
                    }
                }
            }else if(i == inCount + 2){
                tempCell4.setCellValue("收入合计");
            }else if(i == inCount + 3){
                tempCell4.setCellValue("税额");
            } else if(i >= inCount + 4 && i < cell - 3){
                for (int k = 0; k < costOutList.size(); k++) {
                    if(i == inCount + 4 + k){
                        tempCell4.setCellValue(costOutList.get(k).getCostName());
                    }
                }
            }else if(i == cell - 3){
                tempCell4.setCellValue("支出合计");
            }
        }
        //合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 0, 0));//项目
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 1, 1));//日期
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 2, 2));//税率
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, inCount + 2));//收入
        sheet.addMergedRegion(new CellRangeAddress(3, 3, inCount + 3, cell - 3));//支出
        sheet.addMergedRegion(new CellRangeAddress(3, 4, cell - 2, cell - 2));//利润
        sheet.addMergedRegion(new CellRangeAddress(3, 4, cell - 1, cell - 1));//利润率

        //第六行之后循环数据
        for(TSpCountTax tax : taxList){
            Row row5 = sheet.createRow(rowNum++);
            row5.setHeight((short) 400);
            //循环插入数据
            for (int i = 0; i < cell; i++) {
                Cell tempCell5 = row5.createCell(i);
                tempCell5.setCellStyle(contentStyle);
                if(i == 0){
                    //项目名称
//                    tempCell5.setCellStyle(contentStyle);
                    tempCell5.setCellValue(tax.getProjectName());
                }else if(i == 1){
                    //日期
//                    tempCell5.setCellStyle(contentStyle);
                    tempCell5.setCellValue(this.YearMonthConverter(tax.getCountDate()));
                } else if(i == 2) {
                    //税率
//                    tempCell5.setCellStyle(contentStyle);
                    tempCell5.setCellValue(tax.getTaxRate().doubleValue() + "%");
                } else if(i == 3) {
                    //开票金额
//                    tempCell5.setCellStyle(contentCellStyleLirun);
                    tempCell5.setCellValue(tax.getBillMoney().doubleValue());
                } else if(i >= 4 && i < inCount + 2) {
                    //各项收入
                    List<TSpCountIncome> incomeList = tax.getCountIncomeIncomeList();
                    for (int a = 0; a < costInList.size(); a++) {
                        if(i == 4 + a){
//                            tempCell5.setCellStyle(contentCellStyleLirun);
                            Long costId = costInList.get(a).getCostId();
                            Double cellValue = 0.00;
                            for(int b = 0; b < incomeList.size(); b++){
                                if(incomeList.get(b).getCostId() == costId){
                                    cellValue = incomeList.get(b).getTotalMoney().doubleValue();
                                }
                            }
                            tempCell5.setCellValue(cellValue);
                        }
                    }
                }else if(i == inCount + 2){
                    //收入合计
//                    contentCellStyleHeji.setDataFormat(dataFormat.getFormat("0.00"));
                    tempCell5.setCellStyle(contentCellStyleHeji);
                    tempCell5.setCellValue(tax.getIncomeMoney().doubleValue());
                }else if(i == inCount + 3){
                    //税额
                    tempCell5.setCellValue(tax.getTaxMoney().doubleValue());
                } else if(i >= inCount + 4 && i < cell - 3){
                    //各项支出
                    List<TSpCountIncome> disburseList = tax.getCountIncomeDisburseList();
                    for (int c = 0; c < costOutList.size(); c++) {
                        if(i == inCount + 4 + c){
//                            tempCell5.setCellStyle(contentCellStyleLirun);
                            Long costId = costOutList.get(c).getCostId();
                            Double cellValue = 0.00;
                            for(int b = 0; b < disburseList.size(); b++){
                                if(disburseList.get(b).getCostId() == costId){
                                    cellValue = disburseList.get(b).getTotalMoney().doubleValue();
                                }
                            }
                            tempCell5.setCellValue(cellValue);
                        }
                    }
                }else if(i == cell - 3){
                    //支出合计
//                    contentCellStyleHeji.setDataFormat(dataFormat.getFormat("0.00"));
                    tempCell5.setCellStyle(contentCellStyleHeji);
                    tempCell5.setCellValue(tax.getPayMoney().doubleValue());
                } else if(i == cell - 2) {
                    //利润
                    tempCell5.setCellStyle(contentCellStyleLirun);
                    tempCell5.setCellValue(tax.getProfitMoney().doubleValue());
                } else if(i == cell - 1) {
                    //利润率
                    tempCell5.setCellStyle(contentCellStyleLirun);
                    tempCell5.setCellValue(tax.getProfitRate().doubleValue() + "%");
                }
            }
        }

        //第N行  合计行
        Row row7 = sheet.createRow(rowNum++);
        row7.setHeight((short) 400);
        String colString;//长度转成ABC后的列
        String sumString;//求和公式
        int endCellNum = taxList.size() + 5;//总行数
        for (int i = 0; i < cell; i++) {
            colString = CellReference.convertNumToColString(i);
            sumString = "SUM("+colString+"6:"+colString+endCellNum+")";
            Cell tempCell7 = row7.createCell(i);
            tempCell7.setCellStyle(headerStyleCenter);
            if (i == 0) {
                tempCell7.setCellValue("合计");
            }else if (i == 1) {
                tempCell7.setCellValue("--");
            }else if (i == 2) {
                tempCell7.setCellValue("--");
            }else if(i == cell - 1) {
                //利润率//利润÷收入×100=利润率
                BigDecimal incomeMoney = taxList.stream().map(m -> m.getIncomeMoney()).reduce(BigDecimal.ZERO,BigDecimal :: add);
                BigDecimal profitMoney = taxList.stream().map(m -> m.getProfitMoney()).reduce(BigDecimal.ZERO,BigDecimal :: add);
                BigDecimal profitRate = (BigDecimal.ZERO.compareTo(incomeMoney) == 0 ? BigDecimal.ZERO : profitMoney.divide(incomeMoney, 4, RoundingMode.HALF_UP)).multiply(new BigDecimal(100));
                tempCell7.setCellValue(profitRate.doubleValue() + "%");
            }else  {
                tempCell7.setCellFormula(sumString);
            }
        }

        try {
//            //调用下载     导出
            downloadExcel(response,wb ,"统计.xls");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        BigDecimal incomeMoney = new BigDecimal(208674);
        BigDecimal profitMoney = new BigDecimal(15574.78);

        BigDecimal profitRate = (BigDecimal.ZERO.compareTo(incomeMoney) == 0 ? BigDecimal.ZERO : profitMoney.divide(incomeMoney, 4, RoundingMode.HALF_UP)).multiply(new BigDecimal(100));

        System.out.println(profitRate);
    }


    //下载
    public static void downloadExcel(HttpServletResponse response, SXSSFWorkbook wb , String fileName) throws IOException {
//        response.reset();
//        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
//        response.addHeader("Content-Disposition", "attachment;filename=fileName" + ".xls");
//        response.setContentType("application/vnd.ms-excel;charset=utf-8");
//        response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName).getBytes(), "iso-8859-1"));
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            wb.setForceFormulaRecalculation(true);//执行公式
            wb.write(out);
//            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(out);
        }

    }



    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    private static CellStyle createTitleCellStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色

        Font headerFont1 = (Font) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("宋体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }


    /**
     * 第二行 样式
     * @param wb
     * @return
     */
    private static CellStyle createTitleCellStyleLeft(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色

        Font headerFont1 = (Font) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("宋体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 14); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }


    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    private static CellStyle createHeadCellStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.LEFT); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        Font headerFont = (Font) wb.createFont(); // 创建字体样式
//        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }


    /**
     * 创建表头样式 居中
     * @param wb
     * @return
     */
    private static CellStyle createHeadCellStyleCenter(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        Font headerFont = (Font) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }


    /**
     * 创建表头样式 居中  利润
     * @param wb
     * @return
     */
    private static CellStyle createHeadCellStyleCenterLirun(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        Font headerFont = (Font) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }



    /**
     * 创建内容样式
     * @param wb
     * @return
     */
    private static CellStyle createContentCellStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成10号字体
        Font font = wb.createFont();
        font.setBold(true); //字体加粗
        font.setColor((short)8);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 收入合计支出合计样式
     * @param wb
     * @return
     */
    private static CellStyle createContentCellStyleHeji(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex());//背景颜色

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成10号字体
        Font font = wb.createFont();
        font.setBold(true); //字体加粗
        font.setColor((short)8);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);

        return cellStyle;
    }


    /**
     * 利润样式
     * @param wb
     * @return
     */
    private static CellStyle createContentCellStyleLirun(SXSSFWorkbook wb) {
//        CellStyle cellStyle = wb.createCellStyle();
//        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
//        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
//        cellStyle.setWrapText(true);// 设置自动换行
//        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
//        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
//        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
//        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
//        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());//背景颜色

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成10号字体
        Font font = wb.createFont();
        font.setBold(true); //字体加粗
        font.setColor((short)8);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);

        return cellStyle;
    }

    private String YearMonthConverter(String str ){
        DateTime yyyyMM = DateUtil.parse(str, "yyyyMM");
        // 使用格式化器格式化YearMonth对象
        String formatted = DateUtil.format(yyyyMM,"yyyy年M月");
        return formatted;
    }

}
